﻿
Public Class FrmRecherche
    Dim position As Integer = 0
    Dim macnx As New PPE3.ClassMySqlConnect("853339_depanneur", "071064")
    Dim co As Boolean

#Region " Chargement du formulaire"

    Private Sub FrmRecherche_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.MdiParent = FrmStart
        Me.Left = 0
        Me.Top = 0

        Connection()

    End Sub

#End Region

#Region " Gestion de la recherche Rapide "

    Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles recherprod.TextChanged

        macnx.Se_Connecter_MySqlServer()

        DgOccasResultat.DataSource = Nothing
        macnx.viderVue("Toutmateriel")
        DgOccasResultat.DataSource = macnx.Executer_Requete_Select("select idmateriel,nommateriel,prixmateriel,quantiter,reference,numerocategories,nomcategorie from materielOcass,categoriemateriel where (materielOcass.numeroCategories=categoriemateriel.NumeroCategorie) and NomMateriel LIKE '%" & recherprod.Text & "%'", "Toutmateriel")

        macnx.Se_Deconnecter_MySqlServer()

    End Sub

    Private Sub ResetRechercheR_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ResetRechercheR.Click
        recherprod.Text = ""
        RechargerLesDonnees()
    End Sub

#End Region

#Region " Gestion de la recherche détailler"

    Private Sub CbCategorie_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CbCategorie.TextChanged, CbMarque.SelectedValueChanged, TxtReference.TextChanged
        If co <> True Then
            Dim cb As String
            Dim cat As String
            Dim tt As String

            Try
                cb = CbMarque.Text
                cat = CbCategorie.Text
                tt = TxtReference.Text
            Catch ex As InvalidCastException
                Exit Sub
            End Try

            macnx.Se_Connecter_MySqlServer()

            DgOccasResultat.DataSource = Nothing
            macnx.viderVue("Toutmateriel")
            DgOccasResultat.DataSource = macnx.Executer_Requete_Select("select idmateriel,nommateriel,prixmateriel,quantiter,reference,numerocategories,nomcategorie from materielOcass,categoriemateriel where (materielOcass.numeroCategories=categoriemateriel.NumeroCategorie) and NomMateriel LIKE '%" & cb & "%' and reference LIKE '%" & tt & "%'and nomcategorie like  '%" & cat & "%'", "Toutmateriel")

            macnx.Se_Deconnecter_MySqlServer()
        End If
    End Sub

    Private Sub ResetRechercheD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ResetRechercheD.Click
        CbCategorie.SelectedIndex = -1
        CbCategorie.Text = ""
        CbMarque.Text = ""
        TxtReference.Text = ""
        RechargerLesDonnees()
    End Sub
#End Region

#Region " Gestion Insertion, suppression , modification"

#Region "Ajouter"

    Private Sub BtnAjouterPO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAjouterPO.Click
        PnlSupprimer.Visible = False
        PnlModif.Visible = False

        If BtnAjouterPO.Text = "Ajouter" Then

            Txtidmateriel.Text = macnx.Generer_Cle_Primaire("idmateriel", "materielOcass")


            txtDesignation.Text = ""
            txtreference2.Text = ""
            txtquantiter.Text = ""
            TxtPrix.Text = ""

            BtnModifierPO.Enabled = False
            BtnSupprimerPO.Enabled = False
            MessageBox.Show("Attention ! , Pour ajouter un matériel veuillez remplir tous les champs !", "ATTENTION", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            BtnAjouterPO.Text = "Valider!"
        Else
            Dim reponse = MessageBox.Show("Vous êtes sur le point d'ajouter un nouveau matériel, en êtes vous bien sur ...?!", "Insertion", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)
            If reponse = vbOK Then
                If txtDesignation.Text <> "" Then
                    Dim a As Integer
                    Dim b As String
                    Dim c As String
                    Dim d As String
                    Dim i As String
                    Dim f As String
                    Dim g As String
                    Dim h As String
                    Dim j As String

                    a = CInt(Txtidmateriel.Text)
                    b = CbMarque2.Text.ToString
                    c = txtDesignation.Text
                    d = CbCategorie2.SelectedValue.ToString
                    i = txtreference2.Text.ToString
                    f = TxtTaille2.Text
                    g = TxtPrix.Text
                    j = txtquantiter.Text

                    h = b & " " & c & " " & f

                    macnx.Se_Connecter_MySqlServer()
                    macnx.Executer_Requete_Action("INSERT INTO materielOcass VALUES ( " & a & ", '" & h & "'," & g & "," & j & "," & d & ",'" & i & "' )")
                    macnx.Se_Deconnecter_MySqlServer()

                    position = 0
                    RechargerLesDonnees()
                    BtnModifierPO.Enabled = True
                    BtnSupprimerPO.Enabled = True
                    BtnAjouterPO.Text = "Ajouter"
                Else
                    MessageBox.Show("Erreur de saisie...!", "   ALERTE!", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End If
            Else
                BtnModifierPO.Enabled = True
                BtnSupprimerPO.Enabled = True
                BtnAjouterPO.Text = "Ajouter"
            End If
        End If
    End Sub
#End Region

#Region "Modifier"

    Private Sub BtnModifierPO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnModifierPO.Click

        PnlModif.Visible = True

        If BtnModifierPO.Text = "Modifier" Then
            idModif.Text = ""
            DesignationModif.Text = ""
            ReferenceModif.Text = ""
            QuantitéModif.Text = ""
            PrixModif.Text = ""

            MessageBox.Show("Attention ! , Pour modifier un matériel veuillez selectionnez la ligne correspondant à celui-ci !", "Attention", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

            BtnAjouterPO.Enabled = False
            BtnSupprimerPO.Enabled = False
            BtnModifierPO.Text = "Valider!"
        Else
            If Txtidmateriel.Text <> "" Then

                Dim a As Integer
                Dim b As String
                Dim c As String
                Dim d As String
                Dim r As String
                Dim f As String
                Dim g As String

                Dim reponse = MessageBox.Show("Le produit va d'être MODIFIER..!", "MODIFICATION", MessageBoxButtons.YesNo, MessageBoxIcon.Information)

                a = CInt(idModif.Text)
                b = DesignationModif.Text
                c = cbcategorieModif.SelectedValue.ToString
                d = ReferenceModif.Text
                r = TxtPrix.Text
                f = txtquantiter.Text




                If reponse = vbYes Then

                    macnx.Se_Connecter_MySqlServer()
                    macnx.Executer_Requete_Action("UPDATE materielOcass Set NomMateriel='" & b & "',PrixMateriel=" & r & ",Quantiter=" & f & ",numeroCategories=" & c & ",reference='" & d & "' where idMateriel = " & a & "")
                    macnx.Se_Deconnecter_MySqlServer()

                    RechargerLesDonnees()

                    BtnModifierPO.Text = "Modifier"

                Else
                    idModif.Text = ""
                    DesignationModif.Text = ""
                    ReferenceModif.Text = ""
                    QuantitéModif.Text = ""
                    PrixModif.Text = ""

                    BtnModifierPO.Text = "Modifier"
                    BtnAjouterPO.Enabled = True
                    BtnSupprimerPO.Enabled = True
                End If
            End If
        End If
    End Sub


#End Region

#Region "Supprimer "

    Private Sub BtnSupprimerPO_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSupprimerPO.Click

        PnlSupprimer.Visible = True

        If BtnSupprimerPO.Text = "Supprimer" Then
            idmaterielSupprimer.Text = ""
            DésignationSupprimer.Text = ""
            ReferenceSurpprimer.Text = ""
            QuantitéSupprimer.Text = ""
            PrixSupprimer.Text = ""

            MessageBox.Show("Attention ! , Pour supprimer un matériel veuillez sélectionnez la ligne correspondant à celui-ci !", "Attention", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            BtnAjouterPO.Enabled = False
            BtnModifierPO.Enabled = False
            BtnSupprimerPO.Text = "Valider!"
        Else
            If Txtidmateriel.Text <> "" Then
                Dim nu As Integer
                nu = Txtidmateriel.Text

                Dim reponse = MessageBox.Show("Le matériel va être SUPPRIMER, confirmez vous cet suppression...?!", "SUPPRESSION", MessageBoxButtons.OKCancel, MessageBoxIcon.Information)

                If reponse = vbOK Then

                    macnx.Se_Connecter_MySqlServer()
                    macnx.Executer_Requete_Action("Delete from materielOcass Where idmateriel= " & nu & "")
                    macnx.Se_Deconnecter_MySqlServer()

                    position = 0
                    RechargerLesDonnees()

                    BtnSupprimerPO.Text = "Supprimer"

                Else
                    idmaterielSupprimer.Text = ""
                    DésignationSupprimer.Text = ""
                    ReferenceSurpprimer.Text = ""
                    QuantitéSupprimer.Text = ""
                    PrixSupprimer.Text = ""

                    BtnAjouterPO.Enabled = True
                    BtnModifierPO.Enabled = True
                    BtnSupprimerPO.Text = "Supprimer"
                End If

            End If
        End If
    End Sub

#End Region

#End Region

#Region " Bilan "

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnBilanStock.Click
        Dim requete As String
        Dim requete2 As String

        macnx.Se_Connecter_MySqlServer()

        requete = macnx.ExecuteScalar("SELECT COUNT( idmateriel ) FROM materiel")
        requete2 = macnx.ExecuteScalar("SELECT sum( quantiter ) FROM materiel")

        macnx.Se_Deconnecter_MySqlServer()
        MessageBox.Show("Vous avez " & requete & " pièces d'occasion en stock et  " & requete2 & " en quantité global de pièces", "INFORMATION", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub


#End Region

#Region " utile"

    Private Sub RechargerLesDonnees()
        Connection()
    End Sub


    Private Sub DgOccasResultat_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DgOccasResultat.CellClick
        If DgOccasResultat.CurrentCell.Value Is DBNull.Value Then
            Txtidmateriel.Enabled = False

        Else
            txtDesignation.Enabled = True
            Txtidmateriel.Enabled = False

            Txtidmateriel.Text = DgOccasResultat.CurrentRow.Cells(0).Value()
            idmaterielSupprimer.Text = DgOccasResultat.CurrentRow.Cells(0).Value()
            idModif.Text = DgOccasResultat.CurrentRow.Cells(0).Value()

            txtDesignation.Text = DgOccasResultat.CurrentRow.Cells(1).Value()
            DésignationSupprimer.Text = DgOccasResultat.CurrentRow.Cells(1).Value()
            DesignationModif.Text = DgOccasResultat.CurrentRow.Cells(1).Value()

            TxtPrix.Text = DgOccasResultat.CurrentRow.Cells(2).Value()
            PrixSupprimer.Text = DgOccasResultat.CurrentRow.Cells(2).Value()
            PrixModif.Text = DgOccasResultat.CurrentRow.Cells(2).Value()

            txtquantiter.Text = DgOccasResultat.CurrentRow.Cells(3).Value()
            QuantitéSupprimer.Text = DgOccasResultat.CurrentRow.Cells(3).Value()
            QuantitéModif.Text = DgOccasResultat.CurrentRow.Cells(3).Value()

            txtreference2.Text = DgOccasResultat.CurrentRow.Cells(6).Value()
            ReferenceSurpprimer.Text = DgOccasResultat.CurrentRow.Cells(6).Value()
            ReferenceModif.Text = DgOccasResultat.CurrentRow.Cells(6).Value()

        End If
    End Sub

    'Private Sub CbCategorie2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
    '    Dim cat2 As Integer
    '    Try
    '        cat2 = CbCategorie2.Text
    '    Catch ex As InvalidCastException
    '        Exit Sub
    '    End Try
    '    connection.MaRequete = "select idmateriel,nommateriel,prixmateriel,quantiter,reference,numerocategories,nomcategorie from materiel,categoriemateriel where (materiel.numeroCategories=categoriemateriel.NumeroCategorie) and nomcategorie like  '" & cat2 & "%'"
    '    connection.MaCommandeSpecialRequete.CommandText = connection.MaRequete
    '    connection.MonRemplisseur.SelectCommand = connection.MaCommandeSpecialRequete
    '    connection.MonJeuDeDonnees.Tables("Toutmateriel").Clear()
    '    connection.MonRemplisseur.Fill(connection.MonJeuDeDonnees, "Toutmateriel")
    '    DgOccasResultat.DataSource = connection.MonJeuDeDonnees.Tables("Toutmateriel")
    '    RafraichirListe()
    'End Sub
#End Region

    Private Sub BtnPlus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPlus.Click
        Dim fC As New CategorieAjout
        fC.Show()
    End Sub

    Public Sub Connection()
        co = True

        macnx.Se_Connecter_MySqlServer()

        macnx.viderVue("Tousmateriels")
        DgOccasResultat.DataSource = Nothing
        DgOccasResultat.DataSource = macnx.Executer_Requete_Select("select IdMateriel,NomMateriel,PrixMateriel,Quantiter,NomCategorie,numerocategories,reference from materielOcass,categoriemateriel where (materielOcass.numeroCategories=categoriemateriel.NumeroCategorie) order by idmateriel asc", "Tousmateriels")
        DgOccasResultat.Columns(5).Visible = False

        macnx.viderVue("gb")
        macnx.viderVue("hb")
        macnx.viderVue("lo")
        macnx.Se_Deconnecter_MySqlServer()

        macnx.Chargement_ComboBox(CbCategorie, "SELECT distinct NomCategorie FROM categoriemateriel", "gb", "NomCategorie", "NomCategorie")
        macnx.Chargement_ComboBox(CbCategorie2, "SELECT distinct numeroCategorie,NomCategorie FROM categoriemateriel", "hb", "numeroCategorie", "nomcategorie")
        macnx.Chargement_ComboBox(cbcategorieModif, "SELECT distinct numeroCategorie,NomCategorie FROM categoriemateriel", "lo", "numeroCategorie", "nomcategorie")

        PnlSupprimer.Visible = False
        PnlModif.Visible = False

        macnx.Se_Deconnecter_MySqlServer()
        co = False
    End Sub
End Class
